import sqlite3  
import base64  
import cv2  
import numpy as np  

conn = sqlite3.connect('../test.sqlite')  
cursor = conn.cursor()  

#cursor.execute('''CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY, image BLOB)''')  
#插入图片数据
def add_image_to_database(image_path, image_id):  
   with open(image_path, 'rb') as f:  
       image_data = f.read()  
   image_data_base64 = base64.b64encode(image_data).decode('utf-8')  
   print(type(image_data_base64))
   cursor.execute("UPDATE pet_msg set pet_img = ? where pid = ? ", (image_data_base64,image_id))  
   conn.commit()
   print('插入成功')
#插入用户图片
def add_image_to_users(image_path, image_id):  
   with open(image_path, 'rb') as f:  
       image_data = f.read()  
   image_data_base64 = base64.b64encode(image_data).decode('utf-8')  
   print(type(image_data_base64))
   cursor.execute("UPDATE users set profileimg  = ? where uid = ? ", (image_data_base64,image_id))  
   conn.commit()
   print('插入成功')   
def get_image_from_database(image_id):  
   cursor.execute("SELECT pet_img FROM pet_msg WHERE pid=?", (image_id,))  
   image_data_base64 = cursor.fetchone()[0]  
   #print(type(image_data_base64)) #str类型
   if image_data_base64:  
       image_data = base64.b64decode(image_data_base64)
       #print(type(image_data))
       image = cv2.imdecode(np.frombuffer(image_data, np.uint8), cv2.IMREAD_COLOR)  
       #print(type(image)) 这个类型是'numpy.ndarray'
       return image  
   else:  
       return None  

# 添加图片到数据库  
#add_image_to_database('01.png', 1) 
#add_image_to_database('02.png', 2) 
#add_image_to_database('04.png', 4)
#add_image_to_database('05.png', 5) 
#add_image_to_database('06.png', 6) 
#add_image_to_database('07.png', 7) 
#add_image_to_database('08.png', 8) 
#add_image_to_database('09.png', 9) 
#add_image_to_database('10.png', 10) 
#add_image_to_database('11.png', 11) 
#add_image_to_database('12.png', 12) 
#add_image_to_database('13.png', 13) 
#add_image_to_database('14.png', 14) 
#add_image_to_database('15.png', 15) 
#add_image_to_database('16.png', 16) 
#add_image_to_database('16.png', 16) 
#add_image_to_database('17.png', 17) 
#add_image_to_database('18.png', 18) 
#add_image_to_database('19.png', 19) 
#add_image_to_database('20.png', 20) 
#add_image_to_database('21.png', 21) 
# add_image_to_database('22.png', 22) 
#add_image_to_users('吴胜飞.jpg','5')
#add_image_to_users('admin.png','1')
# 从数据库中获取图片  
image = get_image_from_database(31)  

cv2.imshow('Image', image)  
cv2.waitKey(0)  
cv2.destroyAllWindows()  


